package com.matt.repository.impl;

import com.matt.foundation.vo.weekPaper.WeekPaperAreaVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.WeekPaperRepository;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * *******************************
 * Created by lch.
 * form com.matt
 * on 2018/11/09
 * *******************************
 */
@Service
public class WeekPaperRepositoryImpl extends BaseNativeSqlRepository implements WeekPaperRepository {
    @Override
    public WeekPaperAreaVo getAreaWeekData(String areaId,String date) {
        String querySql = String.format("select a.intoChildNum as intoChildNum,b.absentChildNum as absentChildNum,e.examineChildNum as examineChildNum,f.unExamineChildNum as unExamineChildNum from ");
         //宝宝入园数
         querySql += String.format("(SELECT count(DISTINCT(cr.childrenId)) as intoChildNum FROM  check_record as cr INNER JOIN children as ch ON cr.childrenId=ch.id " +
                 " WHERE (cr.status=1 or cr.status=2) and weekofyear(cr.checkInTime)=weekofyear('%s') and ch.aid='%s') as a ,",date,areaId);
         //请假数
         querySql += String.format("(SELECT count(cr.childrenId) as absentChildNum FROM  check_record as cr INNER JOIN children as ch ON cr.childrenId=ch.id " +
                 " WHERE weekofyear(cr.created)=weekofyear('%s') and cr.status=4 and ch.aid='%s') as b ,",date,areaId);
         //评测数
         querySql += String.format("(SELECT count(1) as examineChildNum from evaluate_record WHERE weekofyear(created)=weekofyear('%s') and areaId='%s') as e ,",date,areaId);
         //待评测数 （天数 > 30）
         querySql += String.format("(SELECT count(1) as unExamineChildNum FROM evaluate_record  as er INNER JOIN children as ch ON er.childrenId=ch.id WHERE TO_DAYS('%s')-TO_DAYS(er.created)>30 and ch.status=1 " +
                 " and er.id=(SELECT id FROM evaluate_record WHERE childrenId=ch.id  ORDER BY created desc LIMIT 1  ) and ch.aid='%s') as f",date,areaId);
         //优秀老师id
         //querySql += String.format("() as g",areaId);

        List<WeekPaperAreaVo> list = sqlObjectList(querySql,WeekPaperAreaVo.class);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public Integer getAreaIntoChildNum(String areaId,String date) {
        String querySql = String.format("select count(DISTINCT(cr.childrenId)) from check_record as cr INNER JOIN children as ch ON cr.childrenId=ch.id " +
                " where (cr.status=1 or cr.status=2) and weekofyear(cr.checkInTime)=weekofyear('%s') and ch.aid='%s' ",date,areaId);
        List list = sqlSingleList(querySql);
        if (list.size() > 0)
            return list.get(0) == null ? 0 : Integer.parseInt(list.get(0).toString());
        else
            return 0;
    }

    @Override
    public Integer getUnExamineChildNumByStaffId(String staffId,String date) {
        String querySql = String.format("SELECT count(1) as unExamineChildNum FROM evaluate_record  as er INNER JOIN children as ch ON er.childrenId=ch.id WHERE TO_DAYS('%s')-TO_DAYS(er.created)>30 and ch.status=1 ",date);
        querySql +=String.format(" and er.id=(SELECT id FROM evaluate_record WHERE childrenId=ch.id  ORDER BY created desc LIMIT 1 )");
        querySql +=String.format(" and er.childrenId IN(select rcc.childrenId from rel_classes_children as rcc inner join rel_classes_teacher as rct on rcc.classesId=rct.classesId where rct.staffId='%s')",staffId);
        List list = sqlSingleList(querySql);
        if (list.size() > 0)
            return list.get(0) == null ? 0 : Integer.parseInt(list.get(0).toString());
        else
            return 0;
    }

    @Override
    public Integer getExamineNum(String staffId,String date) {
        String querySql = String.format("SELECT count(1) as examineChildNum from evaluate_record WHERE weekofyear(created)=weekofyear('%s') and createTeacherId='%s'",date,staffId);
        List list = sqlSingleList(querySql);
        if (list.size() > 0)
            return list.get(0) == null ? 0 : Integer.parseInt(list.get(0).toString());
        else
            return 0;
    }
}
